package DAO.DAOImple;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.DBTool;

import model.Album;
import model.Photo;
import DAO.PhotoDAO;

public class PhotoImple implements PhotoDAO {


	/*
	 * @ 添加照片
	 * @see DAO.PhotoDAO#addPhoto(model.Photo)
	 * @param photo
	 * @result photo
	 */
	public int addPhoto(Photo photo, int albumId) {
		
		String sql = "INSERT INTO T_Photo (photoName, photoPath, smallPath, description, albumId, uploadTime) VALUES (?, ?, ?, ?, ?, ?)";
		
		int result = 0;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setString(1, photo.getPhotoName());
			pstmt.setString(2, photo.getPhotoPath());
			pstmt.setString(3, photo.getSmallPath());
			pstmt.setString(4, "photo");
			pstmt.setInt(5, albumId);
			pstmt.setString(6, (new SimpleDateFormat("yyyy-MM-dd hh-mm-ss").format(new Date())));
			
			int temp = pstmt.executeUpdate();
			if(temp != 0) {
				result = 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}

	/*
	 * @删除照片
	 * (non-Javadoc)
	 * @see DAO.PhotoDAO#deletePhoto(int)
	 */
	public void deletePhoto(int id) {
		
		String sql = "DELETE FROM T_Photo WHERE id = ?";
		
		Photo photo = new Photo();
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
	}

	/*
	 * @得到照片列表
	 * (non-Javadoc)
	 * @see DAO.PhotoDAO#getPhotoList(int)
	 */
	public List<Photo> getPhotoList(int albumId) {
		
		String sql = "SELECT a.*, p.* FROM T_Album as a, T_Photo as p WHERE a.id = p.albumId AND a.id = ?";
		
		Photo photo;
		Album album;
		List<Photo> photoList = new ArrayList<Photo>();
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, albumId);
			rst = pstmt.executeQuery();
			while(rst.next()) {
				photo = new Photo();
				album = new Album();
				album.setId(rst.getInt("a.id"));
				album.setAlbumName(rst.getString("a.albumName"));
				photo.setId(rst.getInt("p.id"));
				photo.setPhotoName(rst.getString("p.photoName"));
				photo.setPhotoPath(rst.getString("p.photoPath"));
				photo.setSmallPath(rst.getString("p.smallPath"));
				photo.setUploadTime(rst.getDate("p.uploadTime"));
				photo.setAlbumId(rst.getInt("p.albumId"));
				photo.setDescription(rst.getString("p.description"));
				
				photoList.add(photo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return photoList;
	}
	
	/*
	 * @根据id得到照片列表
	 * (non-Javadoc)
	 * @see DAO.PhotoDAO#getPhotoListById(int)
	 */
	public List<Photo> getPhotoListById(int photoId) {
		
		String sql = "SELECT p.*, a.id FROM T_Photo as p, T_Album as a Where p.albumId = a.id AND p.id = ?";
		
		Photo photo;
		Album album;
		List<Photo> photoList = new ArrayList<Photo>();
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, photoId);
			rst = pstmt.executeQuery();
			while(rst.next()) {
				photo = new Photo();
				album = new Album();
				album.setId(rst.getInt("a.id"));
				photo.setPhotoName(rst.getString("p.photoName"));
				photo.setPhotoPath(rst.getString("p.photoPath"));
				photo.setAlbumId(rst.getInt("p.albumId"));
				
				photoList.add(photo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return photoList;
	}
	
	public List<Album> getAlbumList() {
		
		String sql = "SELECT * FROM T_Album";
		
		Album album;
		List<Album> albumList = new ArrayList<Album>();
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			rst = pstmt.executeQuery();
			while(rst.next()) {
				album = new Album();
				album.setId(rst.getInt("id"));
				album.setAlbumName(rst.getString("albumName"));
				album.setCreateTime(rst.getDate("createTime"));
				album.setDescription(rst.getString("description"));
				
				albumList.add(album);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return albumList;
	}

}
